package com.example.hw;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class ShopFactory {
	
	private SQLiteDatabase db;					//SQLiteDatabase對象
	private String db_name = "Shop";			//資料庫名
	private String order_tableName = "orders";	//表名
	private String orderDetail_tableName ="orderDetail";
	private String product_tableName = "products";
	private Shop shop ;
	
	public void goCreateTable(ActMain_Hw main){
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();
		shop.onCreate(db);
	}
	
	public String[][] goQueryOrders(ActMain_Hw main){
		
		shop= new Shop(main, db_name);
		//以輔助類獲得類據庫對象,可以開始寫入DB，開啟連結
		db = shop.getWritableDatabase();
		//指標指到的查詢結果
		Cursor result = db.rawQuery("Select * FROM "+ order_tableName + " ORDER BY 2 DESC", null);
		
		int i = 0;
		int[] orderid = new int[result.getCount()];
		String[] orderTime = new String[result.getCount()];
		//把查詢結果列印出來
		while (result.moveToNext()) {
			orderid[i] = result.getInt(0); 		//獲取第一列的值,第一列的索引從0開始
			orderTime[i] = result.getString(1);	//獲取第二列的值
			i++;
		}
	
		String[][] data = new String [result.getCount()][result.getColumnCount()];
		
		for(i = 0 ; i < result.getCount() ; i ++){			
			data[i][0] = Integer.toString(orderid[i]);
			data[i][1] = orderTime[i];
			//System.out.println("ID="+orderid[i]+" date="+orderTime[i]);
		}
		//關閉連結與指標
		result.close();
		db.close();
		return data;
	}
	
	public String[] goQueryOrders(ActOrder main){
		
		shop= new Shop(main, db_name);
		//以輔助類獲得類據庫對象,可以開始寫入DB，開啟連結
		db = shop.getWritableDatabase();
		//指標指到的查詢結果
		Cursor result = db.rawQuery
				("Select * FROM "+ order_tableName + " ORDER BY 1 DESC Limit 1", null);
		
		int i = 0;
		int[] orderid = new int[result.getCount()];
		String[] orderTime = new String[result.getCount()];
		//把查詢結果列印出來
		while (result.moveToNext()) {
			orderid[i] = result.getInt(0); 		//獲取第一列的值,第一列的索引從0開始
			orderTime[i] = result.getString(1);	//獲取第二列的值
			i++;
		}
	
		String[] data = new String [result.getColumnCount()];
		
		for(i = 0 ; i < result.getCount() ; i ++){			
			data[0] = Integer.toString(orderid[i]);
			data[1] = orderTime[i];
			//System.out.println("ID="+orderid[i]+" date="+orderTime[i]);
		}
		//關閉連結與指標
		result.close();
		db.close();
		return data;
	}
	
	public String[][] goQueryProducts(ActOrder main){
		
		shop= new Shop(main, db_name);
		//以輔助類獲得類據庫對象,可以開始寫入DB，開啟連結
		db = shop.getWritableDatabase();
		//指標指到的查詢結果
		Cursor result = db.rawQuery("Select * FROM " + product_tableName, null);
		
		int dataRows = result.getCount();
		int dataCols = result.getColumnCount();
		
		int i = 0;
		int[] productid		= new int[dataRows];
		String[] drinkName 	= new String[dataRows];
		int[] price 	= new int[dataRows];
		String[] date 	= new String[dataRows];
		
		while (result.moveToNext()) {
			productid[i] = result.getInt(0);
			drinkName[i] = result.getString(1);
			price[i] 	= result.getInt(2);
			date[i] 	= result.getString(3);	
			i++;
		}
	
		String[][] data = new String [dataRows][dataCols];
		
		for(i = 0 ; i < result.getCount() ; i ++){			
			data[i][0] = Integer.toString(productid[i]);
			data[i][1] = drinkName[i];
			data[i][2] = Integer.toString(price[i]);
			data[i][3] = date[i];
			//System.out.println("ID="+orderid[i]+" date="+orderTime[i]);
		}
		//關閉連結與指標
		result.close();
		db.close();
		return data;
	}
	
	public String[][] goQueryProducts(ActDrink main){
		
		shop= new Shop(main, db_name);
		//以輔助類獲得類據庫對象,可以開始寫入DB，開啟連結
		db = shop.getWritableDatabase();
		//指標指到的查詢結果
		Cursor result = db.rawQuery("Select * FROM " + product_tableName, null);
		
		int dataRows = result.getCount();
		int dataCols = result.getColumnCount();
		
		int i = 0;
		int[] productid		= new int[dataRows];
		String[] drinkName 	= new String[dataRows];
		int[] price 	= new int[dataRows];
		String[] date 	= new String[dataRows];
		
		while (result.moveToNext()) {
			productid[i] = result.getInt(0);
			drinkName[i] = result.getString(1);
			price[i] 	= result.getInt(2);
			date[i] 	= result.getString(3);	
			i++;
		}
	
		String[][] data = new String [dataRows][dataCols];
		
		for(i = 0 ; i < result.getCount() ; i ++){			
			data[i][0] = Integer.toString(productid[i]);
			data[i][1] = drinkName[i];
			data[i][2] = Integer.toString(price[i]);
			data[i][3] = date[i];
			//System.out.println("ID="+orderid[i]+" date="+orderTime[i]);
		}
		//關閉連結與指標
		result.close();
		db.close();
		return data;
	}
	
	//反查訂單細項
	public String[][] goQueryOrder(ActOrder main,int order_id){
		
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();

		Cursor result = db.rawQuery
				("Select * FROM " + orderDetail_tableName + " Where order_id = " + order_id, null);
		
		int dataRows = result.getCount();
		int dataCols = result.getColumnCount();
		
		int i = 0;
		int[] orderDeatil_ids = new int[dataRows];
		int[] order_ids 	= new int[dataRows];
		int[] product_id	= new int[dataRows];
		int[] nums		= new int[dataRows];
		int[] price 	= new int[dataRows];

		
		while (result.moveToNext()) {
			
			orderDeatil_ids[i] = result.getInt(0);
			order_ids[i]  = result.getInt(1);
			product_id[i] = result.getInt(2);
			nums[i]		= result.getInt(3);
			price[i] 	= result.getInt(4);

			i++;
		}
	
		String[][] data = new String [dataRows][dataCols];
		
		for(i = 0 ; i < result.getCount() ; i ++){			
			data[i][0] = Integer.toString(orderDeatil_ids[i]);
			data[i][1] = Integer.toString(order_ids[i]);
			data[i][2] = Integer.toString(product_id[i]);
			data[i][3] = Integer.toString(nums[i]);
			data[i][4] = Integer.toString(price[i]);

			//System.out.println("ID="+orderid[i]+" date="+orderTime[i]);
		}
		//關閉連結與指標
		result.close();
		db.close();
		return data;
	}
	
	public String[][] goQueryOrder(ActMain_Hw main,int order_id){
		
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();

		Cursor result = db.rawQuery
				("Select * FROM " + orderDetail_tableName + " Where order_id = " + order_id, null);
		
		int dataRows = result.getCount();
		int dataCols = result.getColumnCount();
		
		int i = 0;
		int[] orderDeatil_ids = new int[dataRows];
		int[] order_ids 	= new int[dataRows];
		int[] product_id	= new int[dataRows];
		int[] nums		= new int[dataRows];
		int[] price 	= new int[dataRows];

		
		while (result.moveToNext()) {
			
			orderDeatil_ids[i] = result.getInt(0);
			order_ids[i]  = result.getInt(1);
			product_id[i] = result.getInt(2);
			nums[i]		= result.getInt(3);
			price[i] 	= result.getInt(4);

			i++;
		}
	
		String[][] data = new String [dataRows][dataCols];
		
		for(i = 0 ; i < result.getCount() ; i ++){			
			data[i][0] = Integer.toString(orderDeatil_ids[i]);
			data[i][1] = Integer.toString(order_ids[i]);
			data[i][2] = Integer.toString(product_id[i]);
			data[i][3] = Integer.toString(nums[i]);
			data[i][4] = Integer.toString(price[i]);

			//System.out.println("ID="+orderid[i]+" date="+orderTime[i]);
		}
		//關閉連結與指標
		result.close();
		db.close();
		return data;
	}
	
	public String[] goQueryProducts(ActOrder main,int key){
		//查詢單筆資料
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();
		
		Cursor result = db.rawQuery
				("Select * FROM "+ product_tableName +" WHERE product_id = " + key, null);
		
		int dataCols = result.getColumnCount();
		
		int i = 0;
		String[] data = new String [dataCols];
		
		while (result.moveToNext()) {
			int productid = result.getInt(0);
			String drinkName = result.getString(1);
			int price = result.getInt(2);
			String date = result.getString(3);
			
			data[0] = Integer.toString(productid);
			data[1] = drinkName;
			data[2] = Integer.toString(price);
			data[3] = date;
		}
	
		//關閉連結與指標
		result.close();
		db.close();
		return data;
	}
	
	public String[] goQueryProducts(ActMain_Hw main,int key){
		//查詢單筆資料
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();
		
		Cursor result = db.rawQuery
				("Select * FROM "+ product_tableName +" WHERE product_id = " + key, null);
		
		int dataCols = result.getColumnCount();
		
		int i = 0;
		String[] data = new String [dataCols];
		
		while (result.moveToNext()) {
			int productid = result.getInt(0);
			String drinkName = result.getString(1);
			int price = result.getInt(2);
			String date = result.getString(3);
			System.out.println(productid);
			data[0] = Integer.toString(productid);
			data[1] = drinkName;
			data[2] = Integer.toString(price);
			data[3] = date;
		}
	
		//關閉連結與指標
		result.close();
		db.close();
		return data;
	}
	
	public String[] goQueryProducts(ActOrder main,String drinkname){
		//查詢單筆資料
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();
		
		Cursor result = db.rawQuery
				("Select * FROM "+ product_tableName +" WHERE drinkName = '" + drinkname +"'", null);
		
		int dataCols = result.getColumnCount();
		
		int i = 0;
		String[] data = new String [dataCols];
		
		while (result.moveToNext()) {
			int productid = result.getInt(0);
			String drinkName = result.getString(1);
			int price = result.getInt(2);
			String date = result.getString(3);
			
			data[0] = Integer.toString(productid);
			data[1] = drinkName;
			data[2] = Integer.toString(price);
			data[3] = date;
		}
	
		//關閉連結與指標
		result.close();
		db.close();
		return data;
	}
	
	
	public int goInsertOrders(ActMain_Hw main){
		//以輔助類獲得類據庫對象,可以開始寫入DB，開起連結
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();
		//時間格式
		DateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd k:mm:ss");
		Date date = new Date();
		//放入要寫入的資料，物件陣列
		ContentValues cv = new ContentValues();
		cv.put("date",timeFormat.format(date));
		long insertResult = db.insert(order_tableName, "", cv);
		db.close();
		if(insertResult != -1){
			//寫入成功
			return 1;
		}else{
			//寫入失敗
			return -1;
		}
		
	}
	
	public int goInsertOrders(ActOrder order){
		//以輔助類獲得類據庫對象,可以開始寫入DB，開起連結
		shop= new Shop(order, db_name);
		db = shop.getWritableDatabase();
		//時間格式
		DateFormat timeFormat = new SimpleDateFormat("yyyy-MM-dd k:mm:ss");
		Date date = new Date();
		//放入要寫入的資料，物件陣列
		ContentValues cv = new ContentValues();
		cv.put("date",timeFormat.format(date));
		long insertResult = db.insert(order_tableName, "", cv);
		db.close();
		if(insertResult != -1){
			//寫入成功
			return 1;
		}else{
			//寫入失敗
			return -1;
		}
		
	}
	
	public int goInsertOrderDetail(ActOrder order,int orderDetail_id,int order_id
			,int product_id,int number,int price){
		//以輔助類獲得類據庫對象,可以開始寫入DB，開起連結
		shop= new Shop(order, db_name);
		db = shop.getWritableDatabase();

		
		//放入要寫入的資料，物件陣列
		ContentValues cv = new ContentValues();
		cv.put("orderDetail_id",orderDetail_id);
		cv.put("order_id", order_id);
		cv.put("product_id", product_id);
		cv.put("number", number);
		cv.put("price", price);
		long insertResult = db.insert(orderDetail_tableName, "", cv);
		db.close();
		if(insertResult != -1){
			//寫入成功
			return 1;
		}else{
			//寫入失敗
			return -1;
		}
		
	}
	
	public void goInsertProduct(ActMain_Hw main){
		//以輔助類獲得類據庫對象,可以開始寫入DB，開起連結
		
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();
		String[] drinks = {"紅茶","綠茶","奶茶","青茶","烏龍茶"};
		int[] prices = {15,15,30,20,20};
		DateFormat timeFormat;
		Date date;
		
		for(int i = 0 ; i < drinks.length ;i++){
			timeFormat = new SimpleDateFormat("yyyy-MM-dd k:mm:ss");
			date = new Date();
			ContentValues cv = new ContentValues();
			cv.put("drinkName", drinks[i]);
			cv.put("price", prices[i]);
			cv.put("date",timeFormat.format(date));
			db.insert(product_tableName, "", cv);
		}
		
		db.close();
	}
	
	public void goInsertProduct(ActDrink main,String drinkName,int price, String date){

		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put("drinkName", drinkName);
		cv.put("price", price);
		cv.put("date", date);

		db.insert(product_tableName, "", cv);
		
		db.close();
	}
	
	public void goDelete(ActMain_Hw main){
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();
		
		db.execSQL("DROP TABLE IF EXISTS orders");
		db.execSQL("DROP TABLE IF EXISTS orderDetail");
		db.execSQL("DROP TABLE IF EXISTS products");
		db.close();
	}
	
	public void goUpdateProduct(ActDrink main,int product_id, 
			String drinkName,int price,String date){
		shop= new Shop(main, db_name);
		db = shop.getWritableDatabase();
		//擺放物件，會自動轉型對應的資料庫對應的型態
		ContentValues values = new ContentValues();
		values.put("product_id", product_id);
		values.put("drinkName", drinkName);
		values.put("price", price);
		values.put("date", date);
		//我們的Where條件
		String whereClause = "product_id = ?"; 
		//Where的條件參數
		String[] whereArgs = new String[]{Integer.toString(product_id)};
		
		db.update(product_tableName, values, whereClause, whereArgs);
		db.close();
	}

}
